/*
 * @Author: xzy 1453077527@qq.com
 * @Date: 2025-08-24 00:34:58
 * @LastEditors: xzy 1453077527@qq.com
 * @LastEditTime: 2025-08-26 17:01:56
 * @Description:
 */
package com.xtedu.examproject.dao.impl;

import com.xtedu.examproject.dao.base.ClassesDao;
import com.xtedu.examproject.entity.Classes;
import com.xtedu.examproject.util.DBUtil;
import com.xtedu.examproject.util.PageHelper;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class ClassesDaoImpl implements ClassesDao {
    @Override
    public int insert(Classes classes) throws Exception {
        String sql = "insert into classes (class_name,create_date,course_id,teacher_id,room_id,state) values(?,?,?,?,?,?)";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, classes.getName());
            ps.setDate(2, classes.getCreateDate());
            ps.setInt(3, classes.getCourseId());
            ps.setInt(4, classes.getTeacherId());
            ps.setInt(5, classes.getRoomId());
            ps.setBoolean(6, classes.isState());
            return ps.executeUpdate();
        }
    }

    @Override
    public int update(Classes classes) throws Exception {
        String sql = "update classes set class_name=?,course_id=?,teacher_id=?,room_id=?,state=? where id=?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, classes.getName());
            ps.setInt(2, classes.getCourseId());
            ps.setInt(3, classes.getTeacherId());
            ps.setInt(4, classes.getRoomId());
            ps.setBoolean(5, classes.isState());
            ps.setInt(6, classes.getId());
            return ps.executeUpdate();
        }
    }

    @Override
    public int delete(Classes classes) throws Exception {
        String sql = "delete from classes where id=?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, classes.getId());
            return ps.executeUpdate();
        }
    }

    @Override
    public int deleteById(int id) throws Exception {
        return 0;
    }

    @Override
    public Classes findById(int id) throws Exception {
        String sql = "select * from classes where id=?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, id);
            ResultSet rs = ps.executeQuery();
            Classes classes = new Classes();
            while (rs.next()) {
                classes.setId(rs.getInt("id"));
                classes.setName(rs.getString("class_name"));
                classes.setCreateDate(rs.getDate("create_date"));
                classes.setCourseId(rs.getInt("course_id"));
                classes.setTeacherId(rs.getInt("teacher_id"));
                classes.setRoomId(rs.getInt("room_id"));
                classes.setState(rs.getBoolean("state"));
            }
            return classes;
        }
    }

    @Override
    public List<Classes> findAll() throws Exception {
        String sql = "select c.id AS class_id,c.class_name,s.id AS subject_id,s.sub_name AS subject_name,co.id AS course_id,co.course_name,t.id AS teacher_id,t.teacher_name,cr.id AS room_id,cr.room_name,c.state AS class_state,c.create_date from classes c left join courses co ON c.course_id = co.id LEFT JOIN subjects s ON co.subject_id = s.id LEFT JOIN teachers t ON c.teacher_id = t.id LEFT JOIN class_rooms cr ON c.room_id = cr.id";
        List<Classes> classesList = new ArrayList<Classes>();
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                Classes classes = new Classes();
                classes.setId(rs.getInt("class_id"));
                classes.setName(rs.getString("class_name"));
                classes.setSubjectId(rs.getInt("subject_id"));
                classes.setSubjectName(rs.getString("subject_name"));
                classes.setCourseId(rs.getInt("course_id"));
                classes.setCourseName(rs.getString("course_name"));
                classes.setTeacherId(rs.getInt("teacher_id"));
                classes.setTeacherName(rs.getString("teacher_name"));
                classes.setRoomId(rs.getInt("room_id"));
                classes.setRoomName(rs.getString("room_name"));
                classes.setState(rs.getBoolean("class_state"));
                classes.setCreateDate(rs.getDate("create_date"));
                classesList.add(classes);
            }
            return classesList;
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public PageHelper findAllByPage(int pageindex, int pagesize) throws Exception {
        String sql = "select c.id AS class_id,c.class_name,s.id AS subject_id,s.sub_name AS subject_name,co.id AS course_id,co.course_name,t.id AS teacher_id,t.teacher_name,cr.id AS room_id,cr.room_name,c.state AS class_state,c.create_date from classes c left join courses co ON c.course_id = co.id LEFT JOIN subjects s ON co.subject_id = s.id LEFT JOIN teachers t ON c.teacher_id = t.id LEFT JOIN class_rooms cr ON c.room_id = cr.id limit ?,?";
        List<Classes> classesList = new ArrayList<Classes>();
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, (pageindex - 1) * pagesize);
            ps.setInt(2, pagesize);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                Classes classes = new Classes();
                classes.setId(rs.getInt("class_id"));
                classes.setName(rs.getString("class_name"));
                classes.setSubjectId(rs.getInt("subject_id"));
                classes.setSubjectName(rs.getString("subject_name"));
                classes.setCourseId(rs.getInt("course_id"));
                classes.setCourseName(rs.getString("course_name"));
                classes.setTeacherId(rs.getInt("teacher_id"));
                classes.setTeacherName(rs.getString("teacher_name"));
                classes.setRoomId(rs.getInt("room_id"));
                classes.setRoomName(rs.getString("room_name"));
                classes.setState(rs.getBoolean("class_state"));
                classes.setCreateDate(rs.getDate("create_date"));
                classesList.add(classes);
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        }

        String sql1 = "select ceil(count(*)/?) from classes";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps1 = conn.prepareStatement(sql1)) {
            ps1.setInt(1, pagesize);
            ResultSet rs1 = ps1.executeQuery();
            int totalPages = 0;
            if (rs1.next()) {
                totalPages = rs1.getInt(1);
            }
            return new PageHelper<>(classesList, pageindex, pagesize, totalPages, "", pageindex);
        }
    }

    @Override
    public PageHelper findPageByRoleIds(int pageindex, int pagesize, int... roleIds) throws Exception {
        return null;
    }

    @Override
    public PageHelper findByConditionByPage(int pageindex, int pagesize, int classId, String className, int subjectId, int courseId, int teacherId, int roomId) throws Exception {
        StringBuilder sqlBuilder = new StringBuilder("select c.id AS class_id,c.class_name,s.id AS subject_id,s.sub_name AS subject_name,co.id AS course_id,co.course_name,t.id AS teacher_id,t.teacher_name,cr.id AS room_id,cr.room_name,c.state AS class_state,c.create_date from classes c left join courses co ON c.course_id = co.id LEFT JOIN subjects s ON co.subject_id = s.id LEFT JOIN teachers t ON c.teacher_id = t.id LEFT JOIN class_rooms cr ON c.room_id = cr.id");
        List<String> conditions = new ArrayList<>();//条件列表
        List<Object> parameters = new ArrayList<>();//传递参数列表
        if (classId != 0) {
            conditions.add("c.id=?");
            parameters.add(classId);
        }
        if (className != null && !className.trim().isEmpty()) {
            conditions.add("c.class_name like ?");
            parameters.add("%" + className + "%");
        }
        if (subjectId != 0) {
            conditions.add("s.id=?");
            parameters.add(subjectId);
        }
        if (courseId != 0) {
            conditions.add("co.id=?");
            parameters.add(courseId);
        }
        if (teacherId != 0) {
            conditions.add("t.id=?");
            parameters.add(teacherId);
        }
        if (roomId != 0) {
            conditions.add("cr.id=?");
            parameters.add(roomId);
        }
        //组装最终SQL语句
        if (!conditions.isEmpty()) {
            sqlBuilder.append(" where ");
            sqlBuilder.append(String.join(" and ", conditions));
        }
        sqlBuilder.append(" limit ?,?");
        String finalSql = sqlBuilder.toString();
        System.out.println(finalSql);
        List<Classes> classesList = new ArrayList<Classes>();
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(finalSql)) {
            for (int i = 0; i < parameters.size(); i++) {
                ps.setObject(i + 1, parameters.get(i));
            }
            ps.setInt(2, (pageindex - 1) * pagesize);
            ps.setInt(3, pagesize);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                Classes classes = new Classes();
                classes.setId(rs.getInt("class_id"));
                classes.setName(rs.getString("class_name"));
                classes.setSubjectId(rs.getInt("subject_id"));
                classes.setSubjectName(rs.getString("subject_name"));
                classes.setCourseId(rs.getInt("course_id"));
                classes.setCourseName(rs.getString("course_name"));
                classes.setTeacherId(rs.getInt("teacher_id"));
                classes.setTeacherName(rs.getString("teacher_name"));
                classes.setRoomId(rs.getInt("room_id"));
                classes.setRoomName(rs.getString("room_name"));
                classes.setState(rs.getBoolean("class_state"));
                classes.setCreateDate(rs.getDate("create_date"));
                classesList.add(classes);
            }
        }catch (Exception e){
            throw new RuntimeException(e);
        }

        StringBuilder sqlBuilder1 = new StringBuilder("select ceil(count(*)/?) from classes c LEFT JOIN courses co ON c.course_id = co.id LEFT JOIN subjects s ON co.subject_id = s.id LEFT JOIN teachers t ON c.teacher_id = t.id LEFT JOIN class_rooms cr ON c.room_id = cr.id ");
        if (!conditions.isEmpty()) {
            sqlBuilder1.append(" where ");
            sqlBuilder1.append(String.join(" and ", conditions));
        }
        String finalSql1 = sqlBuilder1.toString();
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps1 = conn.prepareStatement(finalSql1)) {
            ps1.setInt(1, pagesize);
            for(int i = 0; i < parameters.size(); i++){
                ps1.setObject(i+2, parameters.get(i));
            }
            ResultSet rs1 = ps1.executeQuery();
            int totalPages = 0;
            if (rs1.next()) {
                totalPages = rs1.getInt(1);
            }
            return new PageHelper<>(classesList, pageindex, pagesize, totalPages, "", pageindex);
        }






    }
}
